﻿Imports Microsoft.Office.Interop
Public Class frmImport
    Dim path As String = Application.StartupPath().ToString
    Dim validator As PropertyValidator = New PropertyValidator
    Dim sqlManager As New SQLManager

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ClearAllFields()
    End Sub
    Private Sub KillExcelProcess()
        Try
            Dim Xcel() As Process = Process.GetProcessesByName("EXCEL")
            For Each Process As Process In Xcel
                Process.Kill()
            Next
        Catch ex As Exception
        End Try
    End Sub
    Private Sub getXlFiletest()
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim xlRange As Microsoft.Office.Interop.Excel.Range

        Dim xlCol As Integer
        Dim xlRow As Integer

        Dim strDestination As String
        Dim srtSheet As String
        Dim Data(0 To 100) As String


        With OpenFileDialog1
            .Filter = "Excel Office|*.xls;*.xlsx"
            .FileName = ""
            .ShowDialog()
            strDestination = .FileName
            srtSheet = "Sheet1"
        End With

        If strDestination <> "" Then
            With lvwImport
                .View = View.Details
                .FullRowSelect = True
                .GridLines = True
                .Columns.Clear()
                .Items.Clear()

                xlApp = New Microsoft.Office.Interop.Excel.Application

                xlWorkbook = xlApp.Workbooks.Open(strDestination)
                xlWorkSheet = xlWorkbook.Worksheets(srtSheet)
                xlRange = xlWorkSheet.UsedRange

                If xlRange.Columns.Count > 0 Then
                    If xlRange.Rows.Count > 0 Then

                        For xlRow = 1 To xlRange.Rows.Count
                            For xlCol = 1 To xlRange.Columns.Count
                                Data(xlCol) = xlRange.Cells(xlRow, xlCol).text

                                If xlRow = 1 Then
                                    .Columns.Add((Data(xlCol).ToString))
                                Else
                                    If xlCol = 1 Then
                                        .Items.Add(Data(xlCol).ToString)

                                    Else
                                        .Items(xlRow - 2).SubItems.Add(Data(xlCol).ToString)
                                    End If
                                End If
                            Next
                        Next
                        grpImport.Text = "Total Record(s): " + lvwImport.Items.Count.ToString()
                        xlWorkbook.Close()
                        xlApp.Quit()

                        KillExcelProcess()
                    End If
                End If
            End With
        Else
            MessageBox.Show("No record found.")
        End If


        Dim excel As New Excel.Application()
        Dim excelPath As String = path & "\sssTable.xlsx"
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(excelPath)
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)


        Dim dataTable As New DataTable

        dataTable.Columns.Add("Salary Min", GetType(String))
        dataTable.Columns.Add("Salary Max", GetType(String))
        dataTable.Columns.Add("Monthly Credit", GetType(String))
        dataTable.Columns.Add("SSS ER", GetType(String))
        dataTable.Columns.Add("SSS EE", GetType(String))
        dataTable.Columns.Add("Total", GetType(String))
        dataTable.Columns.Add("EC/ER", GetType(String))
        dataTable.Columns.Add("Total ER", GetType(String))
        dataTable.Columns.Add("Total EE", GetType(String))
        dataTable.Columns.Add("Total Contribution", GetType(String))
        dataTable.Columns.Add("SE/VM/OFW Total Contribution", GetType(String))

        For indexRow As Integer = 7 To 37
            dataTable.Rows.Add(ws.Range("B" & indexRow).Value, _
                                ws.Range("D" & indexRow).Value, _
                                ws.Range("E" & indexRow).Value, _
                                ws.Range("F" & indexRow).Value, _
                                ws.Range("G" & indexRow).Value, _
                                ws.Range("H" & indexRow).Value, _
                                ws.Range("I" & indexRow).Value, _
                                ws.Range("J" & indexRow).Value, _
                                ws.Range("K" & indexRow).Value, _
                                ws.Range("L" & indexRow).Value, _
                                ws.Range("M" & indexRow).Value)


        Next
        wb.Close(True, Type.Missing, Type.Missing)
        excel.Quit()
        'lvwImport.DataBindings = dataTable


    End Sub
    Private Sub getSSS()
        Dim excel As New Excel.Application()
        'Dim excelPath As String = path & "\sssTable.xlsx"
        'Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(excelPath)
        'Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        Dim ctr As Integer
        Dim strDestination As String
        ctr = 1

        With OpenFileDialog1
            .Filter = "Excel Office|*.xls;*.xlsx"
            .FileName = ""
            .ShowDialog()
            strDestination = .FileName
        End With

        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(strDestination)
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)


        lvwImport.Columns.Add("Bracket")
        lvwImport.Columns.Add("Minimum Salary")
        lvwImport.Columns.Add("Maximum Salary")
        lvwImport.Columns.Add("Monthly Salary")
        lvwImport.Columns.Add("Employer Share")
        lvwImport.Columns.Add("Employee Share")
        lvwImport.Columns.Add("Employe Compensation")

        For indexRow As Integer = 7 To 35
            Dim item As ListViewItem
            item = lvwImport.Items.Add(ctr.ToString())
            item.SubItems.Add(ws.Range("B" & indexRow).Value)
            item.SubItems.Add(ws.Range("D" & indexRow).Value)
            item.SubItems.Add(ws.Range("E" & indexRow).Value)
            item.SubItems.Add(ws.Range("F" & indexRow).Value)
            item.SubItems.Add(ws.Range("G" & indexRow).Value)
            item.SubItems.Add(ws.Range("I" & indexRow).Value)
            ctr += 1
        Next
        grpImport.Text = "Total Record(s): " + lvwImport.Items.Count.ToString()
        validator.resizes(lvwImport)
        btnBrowse.Text = "&Update"
    End Sub
    Private Sub getPhi()
        Dim excel As New Excel.Application()
        'Dim excelPath As String = path & "\phiTable.xlsx"
        'Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(excelPath)
        'Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        Dim ctr As Integer
        Dim strDestination As String
        ctr = 1

        With OpenFileDialog1
            .Filter = "Excel Office|*.xls;*.xlsx"
            .FileName = ""
            .ShowDialog()
            strDestination = .FileName
        End With

        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(strDestination)
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        Dim r As Microsoft.Office.Interop.Excel.Range
        r = ws.UsedRange

        lvwImport.Columns.Add("Bracket")
        lvwImport.Columns.Add("Minimum Salary")
        lvwImport.Columns.Add("Maximum Salary")
        lvwImport.Columns.Add("Salary Base")
        lvwImport.Columns.Add("Employer Share")
        lvwImport.Columns.Add("Employee Share")


        For indexRow = 2 To r.Rows.Count
            Dim item As ListViewItem
            item = lvwImport.Items.Add(ws.Range("A" & indexRow).Value)
            item.SubItems.Add(ws.Range("B" & indexRow).Value)
            item.SubItems.Add(ws.Range("C" & indexRow).Value)
            item.SubItems.Add(ws.Range("D" & indexRow).Value)
            item.SubItems.Add(ws.Range("E" & indexRow).Value)
            item.SubItems.Add(ws.Range("F" & indexRow).Value)
        Next

        grpImport.Text = "Total Record(s): " + lvwImport.Items.Count.ToString()
        validator.resizes(lvwImport)
        btnBrowse.Text = "&Update"
    End Sub
    Private Sub getPAGIbig()
        Dim excel As New Excel.Application()
        'Dim excelPath As String = path & "\pagibigTable.xlsx"
        'Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(excelPath)
        'Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

        Dim ctr As Integer
        Dim strDestination As String
        ctr = 1

        With OpenFileDialog1
            .Filter = "Excel Office|*.xls;*.xlsx"
            .FileName = ""
            .ShowDialog()
            strDestination = .FileName
        End With

        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(strDestination)
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        Dim r As Microsoft.Office.Interop.Excel.Range
        r = ws.UsedRange

        lvwImport.Columns.Add("Minimum Salary")
        lvwImport.Columns.Add("Maximum Salary")
        lvwImport.Columns.Add("% Employer Share")
        lvwImport.Columns.Add("% Employee Share")
        lvwImport.Columns.Add("Maximum Share")

        For indexRow = 2 To r.Rows.Count
            Dim item As ListViewItem
            item = lvwImport.Items.Add(ws.Range("A" & indexRow).Value)
            item.SubItems.Add(ws.Range("B" & indexRow).Value)
            item.SubItems.Add(ws.Range("C" & indexRow).Value)
            item.SubItems.Add(ws.Range("D" & indexRow).Value)
            item.SubItems.Add(ws.Range("E" & indexRow).Value)
        Next

        grpImport.Text = "Total Record(s): " + lvwImport.Items.Count.ToString()
        validator.resizes(lvwImport)
        btnBrowse.Text = "&Update"
    End Sub
    Private Sub getTax()
        Dim excel As New Excel.Application()
        'Dim excelPath As String = path & "\taxTable.xlsx"
        'Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(excelPath)
        'Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

        Dim ctr As Integer
        Dim strDestination As String
        ctr = 1

        With OpenFileDialog1
            .Filter = "Excel Office|*.xls;*.xlsx"
            .FileName = ""
            .ShowDialog()
            strDestination = .FileName
        End With

        Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(strDestination)
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(excel.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        Dim r As Microsoft.Office.Interop.Excel.Range
        r = ws.UsedRange

        lvwImport.Columns.Add("Tax Code")
        lvwImport.Columns.Add("Minimum Salary")
        lvwImport.Columns.Add("Maximum Salary")
        lvwImport.Columns.Add("Base Tax")
        lvwImport.Columns.Add("% Over")

        For indexRow = 2 To r.Rows.Count
            Dim item As ListViewItem
            item = lvwImport.Items.Add(ws.Range("A" & indexRow).Value)
            item.SubItems.Add(ws.Range("B" & indexRow).Value)
            item.SubItems.Add(ws.Range("C" & indexRow).Value)
            item.SubItems.Add(ws.Range("D" & indexRow).Value)
            item.SubItems.Add(ws.Range("E" & indexRow).Value)
        Next

        grpImport.Text = "Total Record(s): " + lvwImport.Items.Count.ToString()
        validator.resizes(lvwImport)
        btnBrowse.Text = "&Update"
    End Sub
    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click
        If btnBrowse.Text = "&Import Record" Then
            If Clipboard.GetData(DataFormats.Text) = "SSS" Then
                getSSS()
            ElseIf Clipboard.GetData(DataFormats.Text) = "PHI" Then
                getPhi()
            ElseIf Clipboard.GetData(DataFormats.Text) = "Pag-Ibig" Then
                getPAGIbig()
            ElseIf Clipboard.GetData(DataFormats.Text) = "Tax" Then
                getTax()
            End If
        ElseIf btnBrowse.Text = "&Update" Then
            If Clipboard.GetData(DataFormats.Text) = "SSS" Then
                Dim i As Integer
                For i = 1 To lvwImport.Items.Count
                    Try
                        sqlManager.executeQuery("INSERT INTO tbl_sss (bracket,min_salary,max_salary,monthly_salary,employer_share,employee_share, employee_compensation) VALUE (" + lvwImport.Items(i - 1).SubItems(0).Text + "," + lvwImport.Items(i - 1).SubItems(1).Text + "," + lvwImport.Items(i - 1).SubItems(2).Text + "," + lvwImport.Items(i - 1).SubItems(3).Text + "," + lvwImport.Items(i - 1).SubItems(4).Text + "," + lvwImport.Items(i - 1).SubItems(5).Text + "," + lvwImport.Items(i - 1).SubItems(6).Text + ")")
                    Catch ex As Exception
                        MsgBox("Can't load Web page" & vbCrLf & ex.Message)
                    End Try
                    Me.Close()
                Next
            ElseIf Clipboard.GetData(DataFormats.Text) = "PHI" Then
                Dim i As Integer
                For i = 1 To lvwImport.Items.Count
                    Try
                        sqlManager.executeQuery("INSERT INTO tbl_philhealth (bracket,min_salary, max_salary, base_salary, employer_share, employee_share) VALUE (" + lvwImport.Items(i - 1).SubItems(0).Text + "," + lvwImport.Items(i - 1).SubItems(1).Text + "," + lvwImport.Items(i - 1).SubItems(2).Text + "," + lvwImport.Items(i - 1).SubItems(3).Text + "," + lvwImport.Items(i - 1).SubItems(4).Text + "," + lvwImport.Items(i - 1).SubItems(5).Text + ")")
                    Catch ex As Exception
                        MsgBox("Can't load Web page" & vbCrLf & ex.Message)
                    End Try
                    Me.Close()
                Next

            ElseIf Clipboard.GetData(DataFormats.Text) = "Pag-Ibig" Then
                Dim i As Integer
                For i = 1 To lvwImport.Items.Count
                    Try
                        sqlManager.executeQuery("INSERT INTO tbl_pagibig (min_salary, max_salary, employer_share, employee_share, min_share) VALUE (" + lvwImport.Items(i - 1).SubItems(0).Text + "," + lvwImport.Items(i - 1).SubItems(1).Text + "," + lvwImport.Items(i - 1).SubItems(2).Text + "," + lvwImport.Items(i - 1).SubItems(3).Text + "," + lvwImport.Items(i - 1).SubItems(4).Text + ")")
                    Catch ex As Exception
                        MsgBox("Can't load Web page" & vbCrLf & ex.Message)
                    End Try
                    Me.Close()
                Next
            ElseIf Clipboard.GetData(DataFormats.Text) = "Tax" Then
                Dim i As Integer
                For i = 1 To lvwImport.Items.Count
                    Try
                        sqlManager.executeQuery("INSERT INTO tbl_pagibig (min_salary, max_salary, employer_share, employee_share, min_share) VALUE (" + lvwImport.Items(i - 1).SubItems(0).Text + "," + lvwImport.Items(i - 1).SubItems(1).Text + "," + lvwImport.Items(i - 1).SubItems(2).Text + "," + lvwImport.Items(i - 1).SubItems(3).Text + "," + lvwImport.Items(i - 1).SubItems(4).Text + "," + lvwImport.Items(i - 1).SubItems(5).Text + "," + lvwImport.Items(i - 1).SubItems(6).Text + ")")
                    Catch ex As Exception
                        MsgBox("Can't load Web page" & vbCrLf & ex.Message)
                    End Try
                Next
                MsgBox("Successfully saved.")
            End If
            Me.Close()
        End If
    End Sub
    Public Function ClearAllFields()
        With lvwImport
            .View = View.Details
            .FullRowSelect = True
            .GridLines = True
            .Columns.Clear()
            .Items.Clear()
        End With

        btnBrowse.Text = "&Import Record"
        grpImport.Text = "Total Record(s): 0"
        Return 0
    End Function
End Class